﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace Music_Organizer
{
    class SQLiteManager
    {
        const string dbFileName = "CardEx.s3db";
        public bool newDatabase = false;
        
        SQLiteConnection dbConnection = null;
        SQLiteTransaction dbTransaction = null;

        public SQLiteManager()
        {
            newDatabase = !System.IO.File.Exists(dbFileName);

            dbConnection = new SQLiteConnection("Data Source=" + dbFileName);
            dbConnection.Open();

            dbTransaction = dbConnection.BeginTransaction();

            if (newDatabase)
            {
                InitSQLiteTables();
            }
        }

        public bool Clear()
        {
            bool success = true;

            try
            {
                string DataSource = dbConnection.DataSource;

                dbConnection.Close();
                System.IO.File.Delete(dbFileName);

                dbConnection = new SQLiteConnection("Data Source=" + dbFileName);
                dbConnection.Open();
            }
            catch
            {
                success = false;
            }

            success = InitSQLiteTables() && success;

            return success;
        }

        public bool InitSQLiteTables()
        {
            bool success;

            try
            {
                if (dbTransaction == null)
                    dbTransaction = dbConnection.BeginTransaction();

                SQLiteCommand cloudCMD = new SQLiteCommand("CREATE TABLE Cloud (ID INT, TypeID TEXT, Title TEXT, Path TEXT, Extention TEXT)", dbConnection);
                cloudCMD.ExecuteNonQuery();
                cloudCMD.Dispose();

                SQLiteCommand typeCMD = new SQLiteCommand("CREATE TABLE TypeID (TypeID INT, TypeTable TEXT, TypeDescription TEXT)", dbConnection);
                typeCMD.ExecuteNonQuery();
                typeCMD.Dispose();

                SQLiteCommand musicCMD = new SQLiteCommand("CREATE TABLE Music (ID INT, Artist TEXT, Album Name TEXT, Track INT, Genre TEXT, Year INT)", dbConnection);
                musicCMD.ExecuteNonQuery();
                musicCMD.Dispose();

                SQLiteCommand LocalCMD = new SQLiteCommand("CREATE TABLE Local (ID INT, Path TEXT)", dbConnection);
                LocalCMD.ExecuteNonQuery();
                LocalCMD.Dispose();

                BuildTypeIDTable();

                Commit();

                success = true;
            }
            catch
            {
                success = false;
            }

            return success;
        }
        private void BuildTypeIDTable()
        {
            DataTable DT = new DataTable();
            DT.Columns.Add("TypeID",typeof(int));
            DT.Columns.Add("TypeTable", typeof(string));
            DT.Columns.Add("TypeDescription", typeof(string));

            object[] row = null;
            row =  new object[] {1, "Music", "Music"};
            DT.Rows.Add(row);

            TableInsert(DT, "TypeID");
        }

        public DataTable RemoveDuplicateMusic(DataTable rawImportTable)
        {
            //Confirm that the required columns are present
            if (!rawImportTable.Columns.Contains("Artist") || !rawImportTable.Columns.Contains("Album") || !rawImportTable.Columns.Contains("Genre") || !rawImportTable.Columns.Contains("Path"))
                throw new Exception("Missing required information to sort Music duplicates");

            DataTable sortedImportTable = new DataTable();
            for (int i = 0; i < rawImportTable.Columns.Count; i++)
            {
                sortedImportTable.Columns.Add(rawImportTable.Columns[i].ColumnName);
            }

            for (int i = 0; i < rawImportTable.Rows.Count; i++)
            {
                //Search the database for a copy of this song.
                bool inDataBase = false;
                DataTable DT = Select("Music", "*", "Artist=\"" + (string)rawImportTable.Rows[i]["Artist"] + "\" AND Album=\"" + (string)rawImportTable.Rows[i]["Album"] + "\"");
                if (DT.Rows.Count > 0)
                {
                    inDataBase = true;
                }

                //Search already added songs into newEntry for this song.  Maybe the folder had the song twice.
                bool inNewEntry = false;
                for (int j = 0; j < sortedImportTable.Rows.Count; j++)
                {
                    if ((string)sortedImportTable.Rows[j]["Title"] == (string)rawImportTable.Rows[i]["Title"] &&
                        (string)sortedImportTable.Rows[j]["Artist"] == (string)rawImportTable.Rows[i]["Artist"] &&
                        (string)sortedImportTable.Rows[j]["Album"] == (string)rawImportTable.Rows[i]["Album"])
                    {
                        inNewEntry = true;
                    }
                }

                if (!inDataBase && !inNewEntry)
                {
                    sortedImportTable.Rows.Add(rawImportTable.Rows[i].ItemArray);
                }
            }

            return sortedImportTable;
        }

        public void AutoInsert(DataTable DT, string typeTable)
        {
            DataTable typeDT = Select("TypeID", "TypeID", "TypeTable = \"" + typeTable + "\"");

            int typeID = (int)typeDT.Rows[0][0];

            AutoInsert(DT, typeID);
        }
        public void AutoInsert(DataTable DT, int typeID)
        {
            //Every item is located in the cloud table.
            //Make sure the information is complete for the cloud table.
            if (!DT.Columns.Contains("Title") || !DT.Columns.Contains("Path") || !DT.Columns.Contains("Extention"))
            {
                throw new Exception("AutoInsert requires DT to have column named Title,Path, and Extention");
            }

            //Find how many items are currently in the cloud, new IDs will follow current max ID.
            DataTable currentCloud = Select("Cloud", "ID");
            int maxCurrnetID = -1;
            if (currentCloud.Rows.Count > 0)
            {
                maxCurrnetID = (int)currentCloud.Rows[currentCloud.Rows.Count - 1][0];
            }

            DataTable cloudDT = new DataTable();
            cloudDT.Columns.Add("ID");
            cloudDT.Columns.Add("TypeID");
            cloudDT.Columns.Add("Title");
            cloudDT.Columns.Add("Path");
            cloudDT.Columns.Add("Extention");
            for (int i = 0; i < DT.Rows.Count; i++)
            {
                object[] cloudRow = new object[cloudDT.Columns.Count];
                cloudRow[0] = maxCurrnetID + 1 + i;
                cloudRow[1] = typeID;
                cloudRow[2] = DT.Rows[i]["Title"];
                cloudRow[3] = DT.Rows[i]["Path"];
                cloudRow[4] = DT.Rows[i]["Extention"];
                cloudDT.Rows.Add(cloudRow);
            }

            TableInsert(cloudDT, "Cloud");


            //Add Music type to the Music Table
            if (typeID == 1)
            {
                if (!DT.Columns.Contains("Artist") || !DT.Columns.Contains("Album") || !DT.Columns.Contains("Track") || !DT.Columns.Contains("Genre") || !DT.Columns.Contains("Year"))
                    throw new Exception("Missing data required to do a music table entry");

                DataTable musicDT = new DataTable();
                musicDT.Columns.Add("ID");
                musicDT.Columns.Add("Artist");
                musicDT.Columns.Add("Album");
                musicDT.Columns.Add("Track");
                musicDT.Columns.Add("Genre");
                musicDT.Columns.Add("Year");

                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    object[] musicRow = new object[6];
                    musicRow[0] = maxCurrnetID + 1 + i;
                    musicRow[1] = DT.Rows[i]["Artist"];
                    musicRow[2] = DT.Rows[i]["Album"];
                    musicRow[3] = DT.Rows[i]["Track"];
                    musicRow[4] = DT.Rows[i]["Genre"];
                    musicRow[5] = DT.Rows[i]["Year"];
                    musicDT.Rows.Add(musicRow);
                }

                TableInsert(musicDT, "Music");
            }


            Commit();
        }
        public void TableInsert(DataTable DT, string table)
        {
            TableInsert(DT, table, false);
        }
        public void TableInsert(DataTable DT, string table, bool commit)
        {
            if (dbTransaction == null)
                dbTransaction = dbConnection.BeginTransaction();

            //Builds a string for the insert command
            string columns = "(";
            string values = "(";
            for (int i = 0; i < DT.Columns.Count; i++)
            {
                if (i > 0)
                {
                    columns += ", ";
                    values += ", ";
                }
                columns += DT.Columns[i].ColumnName;
                values += "@" + DT.Columns[i].ColumnName;
            }
            columns += ")";
            values += ")";

            string insertCMD = "INSERT INTO \"" + table + "\" " + columns + " VALUES " + values;

            for (int i = 0; i < DT.Rows.Count; i++)
            {
                SQLiteCommand sqlCMD = new SQLiteCommand(insertCMD, dbConnection);
                for (int j = 0; j < DT.Columns.Count; j++)
                {
                    sqlCMD.Parameters.AddWithValue("@" + DT.Columns[j].ColumnName, DT.Rows[i].ItemArray[j]);
                }
                sqlCMD.ExecuteNonQuery();
            }

            if (commit)
                Commit();
        }

        public DataTable Select(string table, string column, string condition)
        {
            string CMD = "SELECT \"" + column + "\" FROM \"" + table + "\" WHERE " + condition + "";

            DataTable DT = new DataTable();
            SQLiteDataAdapter dbDataAdapter = new SQLiteDataAdapter(CMD, dbConnection);
            dbDataAdapter.Fill(DT);

            return DT;
        }
        public DataTable Select(string table, string column)
        {
            string CMD = "SELECT \"" + column + "\" FROM \"" + table + "\"";

            DataTable DT = new DataTable();
            SQLiteDataAdapter dbDataAdapter = new SQLiteDataAdapter(CMD, dbConnection);
            dbDataAdapter.Fill(DT);

            return DT;
        }
        public DataTable Select(string table)
        {
            string CMD = "SELECT * FROM \"" + table + "\"";

            DataTable DT = new DataTable();
            SQLiteDataAdapter dbDataAdapter = new SQLiteDataAdapter(CMD, dbConnection);
            dbDataAdapter.Fill(DT);

            return DT;
        }
        public DataTable SelectCMD(string cmd)
        {
            DataTable DT = new DataTable();
            SQLiteDataAdapter dbDataAdapter = new SQLiteDataAdapter(cmd, dbConnection);
            dbDataAdapter.Fill(DT);

            return DT;
        }
        public void Commit()
        {
            dbTransaction.Commit();
            dbTransaction = null;
        }
        public void Close()
        {
            dbConnection.Close();
        } 
    }
}
